# -*- coding: gb2312 -*-
import cx_Oracle
import threading,thread
import datetime, time
import types
from Queue import Queue

#initialized variable
file_tns = 'tnsnames.ora'
file_output_dir=r'C:\Users\luffy\Desktop'
file_format = r'\output'
file_Sql = 'exec.sql'
file_column = 'column_name.txt'
list_cn = []
list_thread = []
list_fd_output = []
list_sql = []
user = 'bj_stat'
passwd = ''
queue_output_list = []
queue_log = Queue()
comma = ', '
is_display_row = False

#class SQLThread for each database
class SQLThread(threading.Thread):
    def __init__(self, tns_str):
        self.tns = tns_str
        self.db_name = (self.tns.split('|'))[0].strip()
        super(SQLThread, self).__init__(name = self.db_name)  
    def run(self):   
        if (self.tns.split('|'))[1].strip() == '' :
            print '[ERROR]Invalid tns string in ', self.name
            thread.exit_thread()
        self.db_conn = cx_Oracle.connect(user,passwd,(self.tns.split('|'))[1].strip());
        self.cursor = self.db_conn.cursor()
        for i in range(len(list_sql)):
            self.cursor.execute((list_sql[i]).strip())
            for row in self.cursor:
                row_str = self.db_name
                for value in row:
                    if type(value) is types.IntType or type(value) is types.FloatType or type(value) is types.LongType:
                        value = str(value)
                    elif type(value) is types.NoneType :
                        value = ''
                    elif type(value) is datetime.datetime :
                        value = str(value)[0:10]               
                    row_str = row_str + comma + value
                try:                 
                    queue_output_list[i].put(row_str.strip())
                except :
                    print '[ERROR]', self.name, ' get an error in writing " ', row_str, ' " to file ', str(i)
                else :
                    if is_display_row:
                        print '[INFO]', self.name, ' successfully writing row ', row_str, 'to file ', str(i)
        #close db connection
        self.db_conn.close()
    
if __name__ == '__main__':
    #read Sql
    fd_sql = open(file_Sql, mode='r', buffering=-1)
    for line in fd_sql:
        line = line.strip()
        if line != "" :
            list_sql.append(line)     
    fd_sql.close
    print '----------------------------------------------------------------------'
    
    #prompt the user and password
    prompt =  raw_input("Please input user[bj_stat]:")
    if prompt:
        user = prompt
    passwd =  raw_input("Password:")
    print '----------------------------------------------------------------------'
    
    #read column header file
    fd_cn = open(file_column, mode='r')
    
    #open the output file for each sql
    print "The input SQLs are :"
    for i in range(len(list_sql)) :
        file_output = file_output_dir + file_format + '_' + str(i) + '.csv'
        print '--Input SQL is--', str(i), '-->',list_sql[i],'<--, Output file is-->',  file_output, '<--'
        queue_output_list.append(Queue())
        list_fd_output.append(open(file_output, 'w'))
    column_name = fd_cn.readline()
    #write the column header
    for i in range(len(list_sql)) :
        list_fd_output[i].write(column_name + '\n');
    fd_cn.close()
    
    print '--------------------------------------------------------------------'
    prompt =  raw_input('Display row info?[no]');
    if prompt == 'y' or prompt == 'Y' or prompt == 'Yes' or prompt == 'YES' or prompt == 'yes':
        is_display_row = True
    
    print '--------------------------------------------------------------------'
    fd_tns = open(file_tns, mode='r', buffering=-1)
    #create thread under valuation conditions
    for tns_line in fd_tns:
        #ingore line beginning with '#'
        if tns_line.strip() == '' or (tns_line.strip())[0] == '#':    
            continue
        list_thread.append(SQLThread(tns_line))
        
    #start threads
    for t in list_thread:
        t.start()
        print "[INFO]Thread", t.name, "started"
    
    time.sleep(15)
    for t in list_thread:
        t.join()
        
    #write rows to output file
    for i in range(len(list_sql)) :
        while not queue_output_list[i].empty():
            list_fd_output[i].write(queue_output_list[i].get()+'\n')
        
    print 'Completed!'
    print '--------------------------------------------------------------------'
    #close all file discriptions
    for i in range(len(list_fd_output)) :   
        list_fd_output[i].close()
    fd_tns.close()